IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetMergeCandidates')
	BEGIN
		PRINT 'Dropping Procedure GetMergeCandidates'
		DROP  Procedure  GetMergeCandidates
	END

GO

PRINT 'Creating Procedure GetMergeCandidates'

GO

/*
The spoc gets a list of potentially 'duplicated' users. It does the matching by last names and the first three characters of 
the first name.

Note:
-----------
The fact that users match by the criteria explained above does not necessarily mean they are duplicates.
The person using the Merge tool should look further into the data and make necessary determination.
*/

CREATE Procedure [dbo].[GetMergeCandidates] (
	@lastName VARCHAR(64)
) AS
BEGIN

	set nocount on
	
	declare @agrResourceTypeId int
	declare @humanResourceTypeId int
	declare @ctResourceTypeId int
	
	set @agrResourceTypeId = 1
	set @humanResourceTypeId = 2
	set @ctResourceTypeId = 3
	
	set @lastName = rtrim(ltrim(@lastName))
	
	create table #mergeCandidates(LastName varchar(64), FirstName varchar(64))
	
	insert into #mergeCandidates (LastName, FirstName)
	select LastName, FirstName from AdmPerson where LastName = @lastName

	select distinct		
				ap.Id as PersonId,
				'' as MergeToId,
				case when ap.AdmPersonTypeId = 1 then 'Internal' else 'External' end as PersonType, 
				case when fdu.Username is null then '' else fdu.Username end as Username,
				ap.LastName,
				ap.FirstName,
				case when ap.MiddleName is null then '' else ap.MiddleName end as MiddleName,
				(select count(*)  from UserResourceCache where ResourceTypeId = @agrResourceTypeId and FwkDomainUserId = fdu.Id) as Agreements,
				(select count(*) from UserResourceCache where ResourceTypeId = @humanResourceTypeId  and FwkDomainUserId = fdu.Id) as Humans,
				(select count(*) from UserResourceCache where ResourceTypeId = @ctResourceTypeId and FwkDomainUserId = fdu.Id) as ClinicalTrials,
				case when aip.EmployeeNumber is null then '' else aip.EmployeeNumber end as EmployeeNumber,
				case when org.FullyQualifiedName is null then '' else org.FullyQualifiedName end as Organization,
				case  when fau.Id is null then 'False'
					when (fau.IsActiveBitMask & 4) = 0 then 'False'
					else 'True' end as Registered,	
				case when dp.IsActive = 1 then 'Active' else 'Inactive' end as DomainStatus,
				'' as Comment
	from		AdmPerson ap
	left join	FwkDomainUser fdu
	on			fdu.Id = ap.FwkDomainUserId
	left join   AdmPerson_FwkDomainOrganization_Map orgMap on orgMap.AdmPersonId = ap.Id
	left join	FwkDomainOrganization org on org.Id = orgMap.FwkDomainOrganizationId
	left join	FwkApplicationUser fau on fau.FwkDomainUserId = fdu.Id
	left join	FwkDomainPrincipal dp
		on		fdu.FwkPrincipalId = dp.Id
	left join	AdmInternalPerson aip on aip.AdmPersonId = ap.Id
	where		ap.LastName = @lastName
				and ( -- makes sure there are 'duplicates', matches by first three characters of the first name
						(select count(*) from #mergeCandidates mc1 where substring(ap.FirstName, 1, 3) = substring(mc1.FirstName, 1, 3)) > 1
					) 
	order by	ap.LastName, ap.FirstName
	
	drop table #mergeCandidates
	
END
